Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ivan_abboud
Frequent Visitor

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).

 

ivan_abboud_0-1688468329461.png

 

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

 

ivan_abboud_1-1688468378724.png

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

1 ACCEPTED SOLUTION
v-zhangti
Community Support
Community Support

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)

vzhangti_0-1688625901569.png

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.

 

View solution in original post

2 REPLIES 2
ivan_abboud
Frequent Visitor

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

v-zhangti
Community Support
Community Support

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)

vzhangti_0-1688625901569.png

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.

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.