Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
It seems that this is a common question, but even from reading the many posts I am still stuck so I would really appreciate some assistance!
My model has:
2 dim tabes: Date, Products.
2 fact tables: Sales, Purchases.
I create a Matrix with ProductName from Products on the Row and then SalesQty and StockQty from Sales and Purchases as my values:
This all looks ok, except I have blanks for where no sales or purchase were found:
So from reading up on this issue, I see that the ISBLANK() function can be used, but it needs to be on a measure. So I created a measure over SalesQty:
SalesQtyMeasure = IF(ISBLANK(SUMX(SALES, Sales[SalesQty])), 0, SUMX(SALES, Sales[SalesQty]))
However, when I add this to my Matrix, it changes the blanks to zeros, BUT it also brings in rows where I have no purchase or sales - eg: Oranges.
The above is purely sample data for the purpose of this demonstation. For my actual report this is a big problem as it is adding thousands of rows to the matrix which I do not want to see. But I do need zeros instead of blanks, 1 for the readability of the report but also for 2, I want to do conditional formatting where SalesQty is between 0 and 5 for instance.
The pbix file can be found here: https://www.dropbox.com/s/fi175a301annhpj/FruitDemo.pbix?dl=0
Any assistance here greatly appreciated!
Mark
Solved! Go to Solution.
Hey @MarkSL,
I added a stockqty measure and a hasdata measure and filtered based on hasdata.
StockQtyMeasure = IF(ISBLANK(SUMX(Purchases, Purchases[StockQty])), 0,SUMX(Purchases, Purchases[StockQty]))
HasData = IF(AND(Sales[SalesQtyMeasure]=0,[StockQtyMeasure]=0),"No","Yes")
Hey @MarkSL,
I added a stockqty measure and a hasdata measure and filtered based on hasdata.
StockQtyMeasure = IF(ISBLANK(SUMX(Purchases, Purchases[StockQty])), 0,SUMX(Purchases, Purchases[StockQty]))
HasData = IF(AND(Sales[SalesQtyMeasure]=0,[StockQtyMeasure]=0),"No","Yes")
Hi @Anonymous
That did the trick! Many thanks, really appreciate it 🙂
Cheers
Mark
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |