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

[devenk2]

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 ID	Application Name	IssuePriority	Scan Type
001	App1	High	Threat Modeling
002	App2	Medium	SCA
003	App2	Low	Threat Modeling
004	App3	Medium	SCA
005	App1	High	Threat Modeling

Table2 (Threat Modeling Grades)

Application Name	High	Medium	Low	Weighted Score	Grade
App1	2			16	C
App2			1	5	A

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).

Any help would be appreaciated, thanks!

-Deven

[devenk2 · Accepted solution]

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]))