topic Re: DAX measure that ignores specific row filter in DAX Commands and Tips

DAX measure that ignores specific row filter

jimhick47 — Mon, 30 Oct 2023 21:01:23 GMT

I'm trying to put together some Power BI reports for the company I work in. I'm only a beginner with BI so I'm struggling with one thing which I'm not even sure is possible. The data we're using for the reports comes from a SSAS server and because there are millions of rows importing it isn't really an option due to the refresh times so we're having to use a live connection. Because of this I'm having to use DAX measures to do anything with the data to display the info we need. Two of the tables in the data are set out similar to below:

Table 1:

TaskIdentifier	Aspect ID
2023-1	1
2023-1	2
2023-2	3
2023-2	4
2023-3	5
2023-3	6

Table 2:

Aspect ID	Type	Value
1	Pages	500
2	Type	Horror
3	Pages	250
4	Type	Sci-Fi
5	Pages	300
6	Type	Drama

What I'm trying to get is a matrix visualisation with the 'Type' values as the first row group and the TaskIdentifier as the second (like below):

Pages

Horror

2023-1 500

Sci-Fi

2023-2 250

Drama

2023-3 300

Basically I just want the page values to summarise based on the TaskIdentifier and ignore the Type grouping completely. I've got my visualisation set up like below but I can't quite get the DAX right. Either it shows the total pages for all tasks combined or is just blank.

I would be extremely grateful for any suggestions anyone might have as I've been stuck on this for days now. I can't post the data unfortunately as it's confidential but if you need me to clarify anything then please just ask.

Many thanks

Re: DAX measure that ignores specific row filter

barritown — Tue, 31 Oct 2023 11:58:17 GMT

Hi jimhick47,

You can try something like this:

And in plain text for convenience:

Pages = VAR CurrentID = MAX ( Tbl2[Aspect ID] ) VAR Res = LOOKUPVALUE ( Tbl2[Value], Tbl2[Aspect ID], CurrentID - 1 ) RETURN Res

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: DAX measure that ignores specific row filter

jimhick47 — Tue, 31 Oct 2023 20:43:45 GMT

Hi Alexander

Thanks for having a look at this. This is great but at the moment all the sub-totals for the 'Types' are showing as 0. Is there anyway for the DAX to get them to display correctly? For example:

Pages

Horror 350

2023-1 100

2023-2 150

Sci-Fi 600

2023-3 250

2024-4 350

Also one of the problems with the data we have is that it isn't organised very well and the rows in the second table sometimes have the Type come before the Pages value. For example:

Aspect ID	Type	Value
1	Pages	500
2	Type	Horror
3	Type	Sci-Fi
4	Pages	250
5	Pages	300
6	Type	Drama

When this happens, using your DAX code we get the Type displayed in the matrix where the page numbers should be. Is this something that we can get around?

Thanks again for you help.

Re: DAX measure that ignores specific row filter

barritown — Tue, 31 Oct 2023 21:52:45 GMT

jimhick47,

In order to solve the second problem, you can adjust the measure like that:

Pages = VAR CurrentID = MAX ( Tbl2[Aspect ID] ) VAR LookupID = IF ( ISEVEN ( CurrentID ), CurrentID - 1, CurrentID + 1 ) VAR Res = LOOKUPVALUE ( Tbl2[Value], Tbl2[Aspect ID], LookupID ) RETURN Res

I'll see the sub-total part of the problem when my head is fresh (hopefully tomorrow).

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: DAX measure that ignores specific row filter

barritown — Wed, 01 Nov 2023 22:03:28 GMT

jimhick47,

Though my head isn't fresh at all, I've composed such a measure, which seems to work except for the grand total. I presume this measure will be very slow with a large dataset. Can you give it a try?

Pages = VAR CurrentID = MAX ( Tbl2[Aspect ID] ) VAR CurrentVal = MAX ( Tbl2[Value] ) VAR LookupID = IF ( ISEVEN ( CurrentID ), CurrentID - 1, CurrentID + 1 ) VAR Res = LOOKUPVALUE ( Tbl2[Value], Tbl2[Aspect ID], LookupID ) VAR AffectedIDs = SELECTCOLUMNS ( FILTER ( ALL ( Tbl2 ), Tbl2[Value] = CurrentVal ), "ID", [Aspect ID] ) VAR CalculatedIDs = SELECTCOLUMNS ( ADDCOLUMNS ( AffectedIDs, "ID Values", IF ( ISEVEN ( [ID] ), [ID] - 1, [ID] + 1 ) ), "ID", [ID Values] ) VAR CalculatedValues = SELECTCOLUMNS ( FILTER ( ALL ( Tbl2 ), [Aspect ID] in CalculatedIDs ), "Val", [Value] ) VAR AltRes = SUMX ( CalculatedValues, INT ( [Val] ) ) RETURN IF ( ISFILTERED ( Tbl1[TaskIdentifier] ), Res, AltRes )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: DAX measure that ignores specific row filter

jimhick47 — Thu, 02 Nov 2023 21:18:14 GMT

That's worked great, many thanks for all your help!