topic Re: 2 date slicers in Table in DAX Commands and Tips

2 date slicers in Table

Fikri — Fri, 18 Oct 2024 08:38:52 GMT

I have 2 date slicers.

The main table already connected to 2 different 'calendar' tables. So, A and B above is from different calendar table.
I need to create 'detail' table which contain all the information based on first date slicer (A).
Then another column need to be added into the same detail table but it refer based on second date slicer (B) - for comparison

The main data table is called 'Status'
Currently my dax as below :

1. (A) Receipt Date =

CALCULATE (MAX('Status'[RECEIPT DATE]),USERELATIONSHIP('Status'[Report Version],'Calendar'[Report Version]),ALL('Calendar (2)'))

1. (B) Receipt Date =

// CALCULATE (MAX('Status'[RECEIPT DATE]),USERELATIONSHIP('Status'[Report Version],'Calendar (2)'[Report Version]),ALL('Calendar'))

But the result , is not that i expected :

I need a dax , so the "1. (B) Receipt Date" moved to above, and only show data based on Report Version from slicer A.

Re: 2 date slicers in Table

FreemanZ — Fri, 18 Oct 2024 09:21:58 GMT

hi Fikri ,

try like:

1. (A) Receipt Date =

CALCULATE (MAX('Status'[RECEIPT DATE]),USERELATIONSHIP('Status'[Report Version],'Calendar'[Report Version]),ALL('Calendar (2)'), ALL(Status))

1. (B) Receipt Date =

// CALCULATE (MAX('Status'[RECEIPT DATE]),USERELATIONSHIP('Status'[Report Version],'Calendar (2)'[Report Version]),ALL('Calendar'), ALL(Status))

Re: 2 date slicers in Table

Fikri — Fri, 18 Oct 2024 09:33:12 GMT

Hi FreemanZ ,

It didnt work, now the Report Version ( first column ) are showing all data ,
and the Receipt Date for both A and B, calculated the Max from all report version ( and not based on selected Report Version slicers )

Re: 2 date slicers in Table

FreemanZ — Fri, 18 Oct 2024 09:57:38 GMT

try like:

1. (A) Receipt Date =

CALCULATE (MAX('Status'[RECEIPT DATE]),USERELATIONSHIP('Status'[Report Version],'Calendar'[Report Version]),ALL('Calendar (2)'), ALL(Status), VALUES('Status'[Report Version]))

Re: 2 date slicers in Table

Anonymous — Mon, 21 Oct 2024 09:26:51 GMT

Hi Fikri ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. (A) Receipt Date = VAR _c1reportversion = SELECTEDVALUE ( 'Calendar'[Report Version] ) RETURN CALCULATE ( MAX ( 'Status'[RECEIPT DATE] ), FILTER ( ALLSELECTED ( 'Status' ), 'Status'[Report Version] = _c1reportversion ) )

1. (B) Receipt Date = VAR _c2reportversion = SELECTEDVALUE ( 'Calendar (2)'[Report Version] ) RETURN CALCULATE ( MAX ( 'Status'[RECEIPT DATE] ), FILTER ( ALLSELECTED ( 'Status' ), 'Status'[Report Version] = _c2reportversion ) )

Best Regards