DAX measure that ignores specific row filter

[jimhick47]

Hi

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

[barritown]

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

[jimhick47]

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.

[barritown · Accepted solution]

@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

[jimhick47]

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

[barritown]

@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