Forum Discussion

ivan_abboud's avatar
ivan_abboud
Frequent Visitor
3 years ago
Solved

Dynamic calculated table depending on slicer

Greetings, Community!

I am working with two tables: the first table is the store dimension table, which includes columns such as the store's opening date (representing when it was first opened) and closing date (indicating when the store permanently closed).

 

 

The second table contains daily store sales data, including relevant columns.

 

store table and sales table have a relationship on the Store Name column
I also have a calendar table, both tables have a relationship with the date table, on store closing and date field respectively

If a store does not have any records in the sales table, it could signify that the store is closed or there may be an issue with data retrieval.

 

My objective is to identify stores that have data retrieval issues on a specific day. To achieve this, I have incorporated a date slicer in the report, allowing users to select a particular day. For the selected date, I want to display the names of stores that have a closing date greater than the selected date and do not have any records in the sales table.
for example, if I selected 10 - May, I should get store A 

In summary, I aim to show the store names for the selected day, where the closing date is later than the chosen date, and there are no sales records available for those stores.

How can I achieve that using DAX, I tried to use CALCULATETABLE with SELECTEDVALUE, but It didn't work

Thanks in Advance

Need Help
  • v-zhangti's avatar
    v-zhangti
    3 years ago

    Hi, ivan_abboud 

     

    You can try the following methods.

    Measure = 
Var _table=CALCULATETABLE(VALUES('Sales Table'[Store Name]),FILTER(ALL('Sales Table'),[Date]=SELECTEDVALUE('Date'[Date])))
Var _if=IF(SELECTEDVALUE('Store Table'[Store Name]) in _table,0,1)
Return
IF(SELECTEDVALUE('Store Table'[Closing Date])>SELECTEDVALUE('Date'[Date])&&_if=1,1,0)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

2 Replies

  • v-zhangti's avatar
    v-zhangti
    Community Support
    3 years ago

    Hi, ivan_abboud 

     

    You can try the following methods.

    Measure = 
Var _table=CALCULATETABLE(VALUES('Sales Table'[Store Name]),FILTER(ALL('Sales Table'),[Date]=SELECTEDVALUE('Date'[Date])))
Var _if=IF(SELECTEDVALUE('Store Table'[Store Name]) in _table,0,1)
Return
IF(SELECTEDVALUE('Store Table'[Closing Date])>SELECTEDVALUE('Date'[Date])&&_if=1,1,0)

    Is this the result you expect?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • ivan_abboud's avatar
    ivan_abboud
    Frequent Visitor
    3 years ago

    Hi v-zhangti 
    Thanks for the solution, it solved my issue!
    however, I would like to ask you about a specific part

    _if=IF(SELECTEDVALUE('Store Table'[Store Name]) in _table,0,1)

     I understand that the SELECTEDVALUE for the date is coming from the date slicer, which will be defined by the user, but here when we apply SELECTEDVALUE on the Store Name, how actually it is calculated? what is the context?

    Thanks in advance