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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
devenk2
Frequent Visitor

Fixing (Blank) when Filtering Columns in Many to One Relationship

Hello,

 

I am making a scorecard for some metrics, which requires me to calculate grades for certain applications based on the severities of a number of security incidents. I have two tables, one with all of the security incidents indexed by Incident ID, and another which is a pivot table of the original which is grouped by the unique application name and is filtered by Scan Type.

 

Table1 (All incidents)

Incident IDApplication NameIssuePriorityScan Type
001App1HighThreat Modeling
002App2MediumSCA
003App2LowThreat Modeling
004App3MediumSCA
005App1HighThreat Modeling

 

Table2 (Threat Modeling Grades)

Application NameHighMediumLowWeighted ScoreGrade
App12  16C
App2  15A

 

The two tables have a many to one relationship through the Application Name columns. The Grade column in Table2 is calculated by comparing the Weighted Score to different constants to return a letter grade. [i.e `IF([Weighted Score] < 10, "A")`]. 

 

I have a scorecard page that shows the Grades for each scan type for each application. There is a slicer at the top to select the Application Name. However, not all applications use each scan type. This causes the Card visualization showing the Threat Modeling Grade to return (Blank) when I filter to an Application Name that does not have any Threat Modeling incidents. The card visualization is referencing the Grade column in its Fields. I understand this is happening because when I filter to this application, the grades column is now empty because the column does not exist anymore. For example, if I filter to App3, the above Table2 would be empty. However, I do not know how to reference the column to return a different value than (Blank) when the column itself does not exist. I would like to return a "-" if there are no related incidents. How can I do this?

 

Both the Grade and Weighted Score columns are calculated columns, not measures.

 

DAX code for Grade column (Quartile values are fixed constants):

 

Grade = IF(TMScoreTable[Weighted Score] == 0, "A+",
        IF(TMScoreTable[Weighted Score] >= 1 && TMScoreTable[Weighted Score] <= TMScoreTable[B_Quartile], "A",
        IF(TMScoreTable[Weighted Score] > TMScoreTable[B_Quartile] && TMScoreTable[Weighted Score] <=TMScoreTable[C_Quartile], "B",
        IF(TMScoreTable[Weighted Score] > TMScoreTable[C_Quartile] && TMScoreTable[Weighted Score] <= TMScoreTable[D_Quartile], "C",
         IF(TMScoreTable[Weighted Score] > TMScoreTable[D_Quartile] && TMScoreTable[Weighted Score] <= TMScoreTable[E_Quartile], "D",
         IF(TMScoreTable[Weighted Score] > TMScoreTable[E_Quartile] && TMScoreTable[Weighted Score] <= TMScoreTable[F_Quartile], "E",
         IF(TMScoreTable[Weighted Score] > TMScoreTable[F_Quartile], "F", "-")))))))

 

I have tried to add a conditional IF(ISBLANK(TMScoreTable[Weighted Score]), "-", etc) but it still returns a (Blank)
 
This is an image of my dashboard. The Threat Modeler box is the one in question (DAST in the example) and the black oval is the card showing (Blank).
devenk2_0-1668717849393.png

Any help would be appreaciated, thanks!

 

-Deven

1 ACCEPTED SOLUTION
devenk2
Frequent Visitor

I was able to resolve this by building the following measure which references the grade column.

 

TM Grade Not blank = IF(ISBLANK(FIRSTNONBLANK(TMScoreTable[Grade], TMScoreTable[Grade])), "-", FIRSTNONBLANK(TMScoreTable[Grade], TMScoreTable[Grade]))

View solution in original post

1 REPLY 1
devenk2
Frequent Visitor

I was able to resolve this by building the following measure which references the grade column.

 

TM Grade Not blank = IF(ISBLANK(FIRSTNONBLANK(TMScoreTable[Grade], TMScoreTable[Grade])), "-", FIRSTNONBLANK(TMScoreTable[Grade], TMScoreTable[Grade]))

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.