Show count of live values (based on selected slicer value) on Y axis

[alsm]

PS: sorry, don't know if its DAX question or visualization question

I have rows of data that fall in within valid range

The mneasure I have written (with one of previous replies from @amitchandak )

Live = 
    VAR _NAME = MAX(DetailsTbl[Name])
    VAR _DT = MAX(EomDates[Date])
    VAR filt_headertbl = SUMMARIZE(FILTER(HeaderTbl, [ValidFrom] <= _DT && [ValidTo] >= _DT),HeaderTbl[HeaderKey])
    VAR filt_detailstbl = CALCULATETABLE(VALUES(DetailsTbl[Name]), FILTER(DetailsTbl, DetailsTbl[HeaderKey] in filt_headertbl))
RETURN
    IF(_NAME in filt_detailstbl,1,0)

On Y Axis I want to display Live trades (and then later in legend put currency). However, Name shows count of All (i.e 7) and Live does not filter properly

My Data looks like

HeaderKeyValidFromValidTo

H1	Saturday, 1 January 2022	Saturday, 31 December 2022
H2	Tuesday, 15 February 2022	Monday, 31 October 2022
H3	Thursday, 10 March 2022	Thursday, 15 September 2022
H_FebOnly	Monday, 7 February 2022	Friday, 25 February 2022
H_FebMar	Friday, 25 February 2022	Thursday, 14 April 2022
H4	Saturday, 1 January 2022	Monday, 31 October 2022
H5	Tuesday, 15 February 2022	Monday, 31 October 2022

DetailsKeyHeaderKeyNameCurrency

D1	H1	A	EUR
D2	H2	B	GBP
D3	H3	C	AUD
D4	H_FebOnly	D	GBP
D5	H_FebMar	E	USD
D6	H4	F	EUR
D7	H5	G	GBP

How do I display correct number of trades by currency for selected date?

[alsm · Accepted solution]

Somehow now in hindsight its obvious. My DAX expression should have COUNT (and not IF, which only checks if its live or not)

CountLive = 
VAR _selected_val = SELECTEDVALUE(EomDates[Date].[Date])
VAR filt_headertbl = SUMMARIZE(FILTER(HeaderTbl, [ValidFrom] <= MAX(EomDates[Date]) && [ValidTo] >= MAX(EomDates[Date])),HeaderTbl[HeaderKey])
VAR count_live_bonds = CALCULATE(COUNT(DetailsTbl[DetailsKey]), DetailsTbl[HeaderKey] in filt_headertbl)
RETURN 
    count_live_bonds

so now screenshots look as desired

[tamerj1]

Hi @alsm 

please try

Live =
CALCULATE (
VAR _NAME =
MAX ( DetailsTbl[Name] )
VAR _DT =
MAX ( EomDates[Date] )
VAR filt_headertbl =
SUMMARIZE (
FILTER ( HeaderTbl, [ValidFrom] <= _DT && [ValidTo] >= _DT ),
HeaderTbl[HeaderKey]
)
VAR filt_detailstbl =
CALCULATETABLE (
VALUES ( DetailsTbl[Name] ),
FILTER ( DetailsTbl, DetailsTbl[HeaderKey] IN filt_headertbl )
)
RETURN
IF ( _NAME IN filt_detailstbl, 1, 0 ),
CROSSFILTER ( HeaderTbl[HeaderKey], DetailsTbl[HeaderKey], BOTH )
)

[alsm]

Hi @tamerj1 , thank you for your reply

nothing changed by surrounding entire existing expression by  CALCUALTED and adding CROSSFILTER 😞