Forum Discussion
Help with Dax
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
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
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