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
StuartSmith
Power Participant
Power Participant

More effective way to have blank card values

I have a report that currently have about 20 cards on it, showing various bits of data, but with the nature of "Cards", it always wants to show the first value of its respective column if there is no filtering.  I therefore have a measure for each card that looks like...

 

5G_in_Country_Measure = IF(ISFILTERED('Carrier Matrix'[Region]) && HASONEVALUE('Carrier Matrix'[Region]) || ISFILTERED('Carrier Matrix'[Country]) && HASONEVALUE('Carrier Matrix'[Country]) || ISFILTERED('Carrier Matrix'[Carrier]) && HASONEVALUE('Carrier Matrix'[Carrier]) || ISFILTERED('Carrier Matrix'[OData__2_Contractstatus/planning]) && HASONEVALUE('Carrier Matrix'[OData__2_Contractstatus/planning]), IF(COUNT('Carrier Matrix'[Key]) =1, LASTNONBLANK('Carrier Matrix'[OData_5GAvailable]," "), " "), " ")
 
That looks at each filter and if the filters are blank, the card will be blank, but if there is only one record (IF(COUNT('Carrier Matrix'[Key]) =1), that record is displayed in the card, but obviously, when the report loads, it have to run lots and lots of measures even before the data is displayed and the loading time of the report is slow.  Until "Cards" support empties, has anyone got a for effective ways of having cards show nothing unless filtering is active?
 
Thanks in advance,
1 ACCEPTED SOLUTION

I was thinking about this last night, and a way to reduce the code to speed up the running of the code...


Country_Measure = IF(ISFILTERED('Carrier Matrix'[Region]) && HASONEVALUE('Carrier Matrix'[Region]) || ISFILTERED('Carrier Matrix'[Country]) && HASONEVALUE('Carrier Matrix'[Country]) || ISFILTERED('Carrier Matrix'[Carrier]) && HASONEVALUE('Carrier Matrix'[Carrier]) || ISFILTERED('Carrier Matrix'[OData__2_Contractstatus/planning]) && HASONEVALUE('Carrier Matrix'[OData__2_Contractstatus/planning]), IF(COUNT('Carrier Matrix'[Key]) =1, LASTNONBLANK('Carrier Matrix'[Country]," "), " "), " ")

 

and thinking about what the code does, it checks to see if any filters are applied and then only displays data in a card if there is only 1 row.  Therefore, I could remove the checking of filter steps and simply count the number of rows, reducing the overhead processing.

 

Country_Measure Test= IF(COUNT('Carrier Matrix'[Key]) =1, LASTNONBLANK('Carrier Matrix'[Country]," "), " ")

 

Does anyone see any issue with the above?  I have tested and seems to work.

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@StuartSmith , I think you have followed this way only. The only thing is you have parenthesis on && statement 

(ISFILTERED('Carrier Matrix'[Region]) && HASONEVALUE('Carrier Matrix'[Region])) || ()

I was thinking about this last night, and a way to reduce the code to speed up the running of the code...


Country_Measure = IF(ISFILTERED('Carrier Matrix'[Region]) && HASONEVALUE('Carrier Matrix'[Region]) || ISFILTERED('Carrier Matrix'[Country]) && HASONEVALUE('Carrier Matrix'[Country]) || ISFILTERED('Carrier Matrix'[Carrier]) && HASONEVALUE('Carrier Matrix'[Carrier]) || ISFILTERED('Carrier Matrix'[OData__2_Contractstatus/planning]) && HASONEVALUE('Carrier Matrix'[OData__2_Contractstatus/planning]), IF(COUNT('Carrier Matrix'[Key]) =1, LASTNONBLANK('Carrier Matrix'[Country]," "), " "), " ")

 

and thinking about what the code does, it checks to see if any filters are applied and then only displays data in a card if there is only 1 row.  Therefore, I could remove the checking of filter steps and simply count the number of rows, reducing the overhead processing.

 

Country_Measure Test= IF(COUNT('Carrier Matrix'[Key]) =1, LASTNONBLANK('Carrier Matrix'[Country]," "), " ")

 

Does anyone see any issue with the above?  I have tested and seems to work.

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.