Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DavidH
Helper I
Helper I

DAX filtering on slicer settings to provide information

Hi,

 

I am struggling with an efficient way to provide information based on slicer selctions and results from the slicer to provide occasional additional information that I need to display.

A simplified view is shown DAX Problem.PNG

Using the slicer to achieve the results is not a problem. However, what I am struggling with is how to display the results for instances where a note exists. So for the above the unique list from the slicer needs additional about the Notes for (efg123) and (uvw678) to be displayed and I would like to show this via a multi-row card. Obviously other selection combinations may or may not require notes to be displayed.

 

I am just struggling with DAX to create a measure to achieve this.....

 

The Service Table is brought in to PowerBI and the Code Columns are unpivoted and a relationship is available between the Notes and Service Tables. I am not able to get a satisfactory way to display the notes.

 

Any suggestions would be useful.

4 REPLIES 4
Anonymous
Not applicable

Hi there.

 

I've got something like this:

 

How to show rows of codes dynamically and notes.PNG

You select the service(s) and/or CodeType(s) and the table filters the Codes to those which are associated with the service(s) and CodeType(s) selected in the slicers. It also shows Notes next to those codes that do have them. Only those codes are visible which are associated with the selections. If one code has more than one note for the same selection, then those are concatenated and displayed in one row. The Associated Codes card below shows all concatenated codes which are associated with the current selections.

 

Here's the model:

 

Model for Service to Code.PNG

 

and here are all measures:

 

Codes[Associated Codes] =
VAR __associatedCodes =
    VALUES ( ServiceToCode[Code] )
VAR __codes =
    CONCATENATEX (
        __associatedCodes,
        ServiceToCode[Code],
        ", ",
        ServiceToCode[Code]
    )
RETURN
    IF ( ISBLANK ( __codes ), "No Codes", __codes )
    
Codes[Code Note(s)] =
VAR __oneCodeFiltered =
    HASONEFILTER ( Codes[Code] )
VAR __visibleCode =
    SELECTEDVALUE ( Codes[Code] )
VAR __isCodePresent = [Is Code Present]
VAR __notes =
    CONCATENATEX ( CodeToNote, CodeToNote[Note], "; " ) & ""
RETURN
    IF ( __oneCodeFiltered && __isCodePresent, __notes )
    
Codes[Is Code Present] =
VAR __codeVisible =
    SELECTEDVALUE ( Codes[Code] )
VAR __codePresent =
    NOT ISEMPTY ( ServiceToCode )
RETURN
    IF ( __codeVisible <> BLANK (), __codePresent, BLANK () )

Please bear in mind that all slicing happens on DIMENSIONS, not on the fact table. Fact table should be hidden or its columns should be hidden. Only measures should be allowed if anything.

 

Best

Darek

Darek,

 

Apologies for delay in getting back to look at your proposed solution. The original relationships were as you had modelled one to many. Strangely I had gone down the concatenatex route and then did a few measures to check in the concatenated values contains any of the selected codes with notes and then had a few measures with if statements to capture the note if applicable.

 

I will investigate your suggestion as I need to expand this in to a more real senario with over 150k services and up to a maximum combination of around 10 notes. With the way I was thinking this would be heavily dependant on a large amount of measures for notes!!

 

Many thanks for responding to the original query!!

Anonymous
Not applicable

By the way... What type of connection is there between Code and Note? Is it one-to-one or one-to-many? Many-to-many? Without knowing the model, no much can be done.

 

Best

Darek

Anonymous
Not applicable

Mate,

 

Please send a snapshot of the model. I wanna see how your tables are connected and what fields they contain.

 

Thanks.

 

Best

Darek

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors