Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I am using Valuelist to built a table
I have 2 dimensions(State and Status) and a single measure using Valuelist as seen below
=If(ValueList('A', 'B','C')= 'A', COUNT({<[status] = {'A','B'},current_month = {'Current'}
>} DISTINCT Id),
If(ValueList('A', 'B','C')= 'B',COUNT({<[status] = {'B'},current_month = {'Current'},[Target Type] = {'B'}
>} DISTINCT Id),
If(ValueList('A', 'B','C')= 'C',COUNT({<[codes] = {">=1"}, [status] = {'B'}>} DISTINCT [Id]),
)))
This gives the following output
Which is correct but I need to add another column sum by the state so that I get an output like
I tried adding sum and total to the above measure but I am not getting the desired output
From my previous experience TOTAL will not work with ValueList.
What you could try is this. Note, I switched the first expression to PickMatch since it should be faster.
Count:
=Pick(Match(ValueList('A', 'B','C'),'A', 'B','C'),
COUNT({<[status] = {'A','B'},current_month = {'Current'}
>} DISTINCT Id),
COUNT({<[status] = {'B'},current_month = {'Current'},[Target Type] = {'B'}
>} DISTINCT Id),
COUNT({<[codes] = {">=1"}, [status] = {'B'}>} DISTINCT [Id]),
)
Sum:
COUNT({<[status] = {'A','B'},current_month = {'Current'}
>} DISTINCT Id) +
COUNT({<[status] = {'B'},current_month = {'Current'},[Target Type] = {'B'}
>} DISTINCT Id) +
COUNT({<[codes] = {">=1"}, [status] = {'B'}>} DISTINCT [Id]),
)