Forum Discussion
Help with Dax
Hi there, I'm new to power bi and working on a sales report. I'm a little stuck with the following problem and was hoping to get some help.
I have a store table that lists all of the stores in our district. I also have a sales table that has the orders and stores we sold to. They are joined together by a store number(primary key in the stores table). I'm trying to create a visualization from a measure that lists the stores we are NOT in. Any help with the DAX or other solutions would be greatly appreciated.
Thank you!
One way is to make table visual with the Stores[Store] column (or whatever you've called it) and a measure like this:
Stores without Sales =
COUNTROWS ( FILTER ( Stores, ISBLANK ( CALCULATE ( COUNTROWS ( Sales ) ) ) ) )This will return blank for all stores that had sales, and give you the count of stores w/o sales (in your table, each Store will have a count of 1 and blanks will be automatically eliminated).
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Here is brief explanation of how it works
Stores without Sales =
COUNTROWS ( FILTER ( Stores, ISBLANK ( CALCULATE ( COUNTROWS ( Sales ) ) ) ) )The FILTER function is an iterator, so it starts with the rows of the Stores table in the current context (i.e., filtered by any slicers or elements on the visuals), it then calculates the rows of the Sales table filtered by the current row (the CALCULATE() triggers "context transition" to do pass the current row as a filter on the Sales table through that relationship). FILTER returns all the rows that evalute as True(), so only the Stores that have no Sales satisfy that condition. Then we just count the rows that pass the filter.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
5 Replies
One way is to make table visual with the Stores[Store] column (or whatever you've called it) and a measure like this:
Stores without Sales =
COUNTROWS ( FILTER ( Stores, ISBLANK ( CALCULATE ( COUNTROWS ( Sales ) ) ) ) )This will return blank for all stores that had sales, and give you the count of stores w/o sales (in your table, each Store will have a count of 1 and blanks will be automatically eliminated).
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
sunnyS , You need to create sales with +0 and filter the sales =0 when plot sales by store
Sales = sum(Table[net sales])+0.
You can use this option and filter for null
You can get using except
except(all(store[store]),all(sales[store]))
refer this file how to use this in clause of measure
https://www.dropbox.com/s/3wr697b3m57zufo/ExceptOther%20Tabl.pbix?dl=0
Thank you mahoneypat. Your solution worked. Quick question: Is your formula cross referencing the sales foreign key to the stores primary key? I'm just trying to figure out how it worked. Thank you
Here is brief explanation of how it works
Stores without Sales =
COUNTROWS ( FILTER ( Stores, ISBLANK ( CALCULATE ( COUNTROWS ( Sales ) ) ) ) )The FILTER function is an iterator, so it starts with the rows of the Stores table in the current context (i.e., filtered by any slicers or elements on the visuals), it then calculates the rows of the Sales table filtered by the current row (the CALCULATE() triggers "context transition" to do pass the current row as a filter on the Sales table through that relationship). FILTER returns all the rows that evalute as True(), so only the Stores that have no Sales satisfy that condition. Then we just count the rows that pass the filter.
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
If you have a Total Sales measure, something like:
Total Sales = SUM(SalesData[Sales])You can create a visual and filter it to show only where Total Sales is blank. You will need to make sure your Store name/number/key is set to show items without data.
So basically, no DAX needed. You just need to filter and set the visual properties
