topic Re: Writing measure to show multiple visuals on the same chart with one slicer in DAX Commands and Tips

Writing measure to show multiple visuals on the same chart with one slicer

char23 — Mon, 12 Aug 2024 23:15:17 GMT

Hello,

I am sorry to keep posting looking for a solution on this. I am trying to write a measure. I have tried multiple methods but cant get anything to work. I would assume having two different measures on the same visual would work. I have two tables (Table1 and Table2). My goal is to use one slicer and show multiple visuals from the same table on the same chart. For example, if a month is selected, I want to show data (filtered with different conditions) for that month, and the month most recent to the selected month.

Column 'Table1'[Month] is linked to Column 'Table2'[Month] with a one to many relationship (Table2-one, Table1-many). Then I have a slicer that uses column 'Table2'[Month]. The visual for my current month works, but now I am trying the add the data to the same visual for its previous month.

For the previous month, If the user selects a month with the slicer, I want to save the [Previous Month] associated in Table2 column as text. So if 24-Jun is selected, I want to save "24-May" as text. Then I want to filter and count the number of rows in Table1 where [Month End] = the saved text & Type = "Task". So in this example, I would count the number of rows in Table1 where Table1[Month End] = "24-May" & Type = "Task", and this would return 1.

Table1

ID	Status	Date Rank	Previous Status	Month	Type	Group	Previous Month
A	Normal	3	No previous	24-Mar	Task	<0	No previous
B	Normal	3	No previous	24-Mar	Task	1 to 5	No previous
C	Not Normal	3	No previous	24-Mar	Task	1 to 5	No previous
D	Not Normal	3	No Previous	24-Mar	Not Task	11 to 15	No previous
A	Not Normal	2	Normal	24-May	Task	6 to 10	24-Mar
B	Normal	2	Normal	24-May	Not Task	6 to 10	24-Mar
C	Normal	2	Not Normal	24-May	Not Task	6 to 10	24-Mar
D	Not Normal	2	Not Normal	24-May	Task	6 to 10	24-Mar
A	Not Normal	1	Not Normal	24-Jun	Task	1 to 5	24-May
B	Not Normal	1	Normal	24-Jun	Task	<0	24-May
D	Normal	1	Normal	24-Jun	Task	<0	24-May
E	Normal	1		24-Jun	Task	1 to 5	24-May

Table2

Date Rank	Month	Next Date Rank	Previous Month
3	24-Mar	4
2	24-May	3	24-Mar
1	24-Jun	2	24-May

Sample of visual I'm trying to create:

Measure that doesn't work for previous month: (I keep getting blank when selecting a current month)

VAR SelectedMonth = SELECTEDVALUE('Table2'[Month])

VAR PreviousMonth1 =

CALCULATE(

MAX('Table2'[Previous Month]),

FILTER(

ALL('Table2'),

'Table2'[Month] = SelectedMonth

)

VAR CountRows1 =

CALCULATETABLE(

'Table1',

FILTER(

ALLSELECTED('Table1'),

'Table1'[Month] = PreviousMonth1 &&

'Table1'[Type] = "Task"

)

RETURN

COUNTROWS(CountRows1)

Measure works for selected month:

VAR SelectedMonth =

CALCULATE(

MAX('Table1'[Date Rank]),

FILTER(

ALL('Table1'),

'Table1'[Month] = SELECTEDVALUE('Table2'[Month])

)

VAR _tablecalculation =

CALCULATETABLE(

'Table1',

ALLSELECTED('Table2'),

'Table1'[Date Rank] = SelectedMonth,

'Table1'[Status] = "Not Normal",

'Table1'[Previous Status] = "Not Normal",

'Table1'[Type] = "Task"

)

RETURN

COUNTROWS(_tablecalculation)

Any help in the right direction is definetly appreciated!

Re: Writing measure to show multiple visuals on the same chart with one slicer

Irwan — Tue, 13 Aug 2024 04:35:32 GMT

hello char23

i see you have post something similar and answered by experts in PBI.

imho, i dont think you can do this (i might be wrong though, i am not an expert).

1. When you selected certain value in slicer, it will remove all values except the selected one. this means, that you can not show 24-June along with 24-May alltogeter when selecting 24-June (because 24-May is filtered out).

2. as far as i know, PBI doesnt have memory so it can not save or store (again, i could be wrong though).

Not sure if this will work for you, but the easiest way to do this is by multiple selection in slicer.

Hope this will help you.

Thank you.

Re: Writing measure to show multiple visuals on the same chart with one slicer

ValtteriN — Tue, 13 Aug 2024 07:51:41 GMT

Hi,

Here is one way to do this:

1. Don't have relation between the tables:

2. Create this kind of filter measure:

Filter for selection =

VAR SelectedMonth = SELECTEDVALUE('Table (41)'[Month])

VAR PreviousMonth1 =

CALCULATE(

MAX('Table (41)'[Previous Month]),

FILTER(

ALL('Table (41)'),

'Table (41)'[Month] = SelectedMonth

)

RETURN

IF(MAX('Table (40)'[Month]) in {SelectedMonth,PreviousMonth1},1,0)

3. Apply it to the visual:

4. Measure:

Measure 29 = VAR SelectedMonth = SELECTEDVALUE('Table (41)'[Month])

var Selected1 = MAX('Table (40)'[Month])

VAR PreviousMonth1 =

CALCULATE(

MAX('Table (41)'[Previous Month]),

FILTER(

ALL('Table (41)'),

'Table (41)'[Month] = SelectedMonth

)

VAR CountRows1 =

COUNTROWS(

CALCULATETABLE(

'Table (40)',

FILTER(

ALLSELECTED('Table (40)'),

'Table (40)'[Month] = PreviousMonth1 &&

'Table (40)'[Type] = "Task" &&

'Table (40)'[Group] = MAX('Table (40)'[Group])

)

))

VAR CountRows2 =

COUNTROWS(CALCULATETABLE(

'Table (40)',

FILTER(

ALLSELECTED('Table (40)'),

'Table (40)'[Month] = SelectedMonth &&

'Table (40)'[Type] = "Task" &&

'Table (40)'[Group] = MAX('Table (40)'[Group])

)

))

RETURN

IF(Selected1=SelectedMonth,

CountRows2, CountRows1)

Here we check if the month in visual is previous -> different calculation logic.

5. End result:

Here I modified the test data to see if it works and it does.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Writing measure to show multiple visuals on the same chart with one slicer

char23 — Tue, 13 Aug 2024 19:57:09 GMT

Thank you very much for your response! This definetly works, and has been a huge help. The only problem I am facing now is how can I modify this to when I click on a bar in the visual, my table visual will update to show those items that are being counted? I understand if not possible with your formula. Right now, when I click on the visual, the table visual only filters for the Month and Group, but does not use the filters that I have inside each measure. Also what are you filtering for group in the visual? In the pic, I see it says "Group is not", but I am not sure what condition you have here.

Re: Writing measure to show multiple visuals on the same chart with one slicer

char23 — Tue, 20 Aug 2024 15:46:40 GMT

Thank you for your response. I have tried this method, the only problem is that the information for the two months selected in my file are being filtered using different measures.