This is difficult for me to explain. I am going to try to simplify as much as I can.
My dataset looks something like this:
I have created another linked table for reporting captions that looks like this:
Notice how my "quantity sold" label does not exist in my fact data - i'm guessing this is the issue...
So i've created this measure:
Report Number:=IF(HASONEVALUE(fRawTrans[Label]),IF(VALUES(dReportLabels[Label])="Quantity Sold",CALCULATE(SUM(fRawTrans[Qty]),ALL(dReportLabels[Label])),CALCULATE(SUM(fRawTrans[Amount]))))
and this one just for troubleshooting purposes:
Count of Labels:=COUNTROWS(VALUES(dReportLabels[Label]))
As you can see in the above results, report number for quantity sold returns blank which tells me that it's failing the has one value test but the count below it shows 1. What am i missing here?
My dataset looks something like this:
Code:
[TABLE="width: 275"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Label[/TD]
[TD]Amount[/TD]
[TD]Qty[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2015[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2015[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]3/31/2015[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]4/30/2015[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2015[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]6/30/2015[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]7/31/2015[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2015[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]9/30/2015[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]10/31/2015[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]11/30/2015[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2015[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2016[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]2/29/2016[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]3/31/2016[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]4/30/2016[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2016[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]6/30/2016[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]7/31/2016[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2016[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]9/30/2016[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]10/31/2016[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]11/30/2016[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2015[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2015[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]3/31/2015[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]4/30/2015[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2015[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]6/30/2015[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]7/31/2015[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2015[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]9/30/2015[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]10/31/2015[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]11/30/2015[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2015[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2016[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]2/29/2016[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]3/31/2016[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]4/30/2016[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2016[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]6/30/2016[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]7/31/2016[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2016[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]9/30/2016[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]10/31/2016[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]11/30/2016[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]B[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2015[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]2/28/2015[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]3/31/2015[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]4/30/2015[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2015[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]6/30/2015[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]7/31/2015[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2015[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]9/30/2015[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]10/31/2015[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]11/30/2015[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2015[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]1/31/2016[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]2/29/2016[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]3/31/2016[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]4/30/2016[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]5/31/2016[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]6/30/2016[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]7/31/2016[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]8/31/2016[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]9/30/2016[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]10/31/2016[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]11/30/2016[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD="align: right"]12/31/2016[/TD]
[TD="align: right"]C[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]8[/TD]
[/TR]
</tbody>[/TABLE]
I have created another linked table for reporting captions that looks like this:
Code:
[TABLE="width: 92"]
<tbody>[TR]
[TD]Label[/TD]
[/TR]
[TR]
[TD]A[/TD]
[/TR]
[TR]
[TD]B[/TD]
[/TR]
[TR]
[TD]C[/TD]
[/TR]
[TR]
[TD]Quantity Sold[/TD]
[/TR]
</tbody>[/TABLE]
So i've created this measure:
Report Number:=IF(HASONEVALUE(fRawTrans[Label]),IF(VALUES(dReportLabels[Label])="Quantity Sold",CALCULATE(SUM(fRawTrans[Qty]),ALL(dReportLabels[Label])),CALCULATE(SUM(fRawTrans[Amount]))))
and this one just for troubleshooting purposes:
Count of Labels:=COUNTROWS(VALUES(dReportLabels[Label]))
Code:
[TABLE="width: 181"]
<tbody>[TR]
[TD][U][B]A[/B][/U][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report Number[/TD]
[TD="align: right"]240[/TD]
[/TR]
[TR]
[TD]Count of Labels[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][U][B]B[/B][/U][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report Number[/TD]
[TD="align: right"]288[/TD]
[/TR]
[TR]
[TD]Count of Labels[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][U][B]C[/B][/U][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report Number[/TD]
[TD="align: right"]384[/TD]
[/TR]
[TR]
[TD]Count of Labels[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][U][B]Quantity Sold[/B][/U][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Report Number[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Count of Labels[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
As you can see in the above results, report number for quantity sold returns blank which tells me that it's failing the has one value test but the count below it shows 1. What am i missing here?