In my powerBI dashboard, I have a data model table ('Report 9') with many colums.
In my table visual I use 4 columns of these data model table, [HUB], [REU], [9. WBS] and [PROJ_CODE].
In my data, I can have blank values in the column [9. WBS] as well as text values, for each [PROJ_CODE].
The problem is that, when I have a text value in my column [9. WBS] for one [PROJ_CODE], I don't care about the blank values of this [PROJ_CODE], this is, I don't want them to appear in my table visual. However, when I have only blank values for the column [9. WBS] for the associated [PROJ_CODE], I want them to appear in my table visual.
All of this conditions have to take into account my Date Slicer selection.
Let's imagine this example:
In my visual (table), I have this columns, [HUB], [REU], [9. WBS], [PROJ_CODE]. Let's imagine that I have the following 5 lines in my table visual, with the respective values in the column order mentioned now, for when my Date Slicer selection is [DATE] = 2023-09 and [DATE] = 2023-08 (this is, there are 2 date selections, the month 08 and 09 of the year 2023):
line 1: "HUB GER", "GER 12", "ABC", "123"
line 2: "HUB GER", "GER 12", "DEF", "123"
line 3: "HUB GER", "GER 12", blank(), "123"
line 4: "HUB GER", "GER 13", "ZXY", "456"
line 5: "HUB GER", "GER 15", blank(), "980"
Consider that there are no more lines for the [PROJ_CODE] values mentioned for [DATE] = 2023-09 and [DATE] = 2023-08.
As you can see, for [PROJ_CODE] = "123", I have 3 different [9. WBS] values - "ABC", "DEF" and blank(). So, for this [PROJ_CODE] and date selection ([DATE] = 2023-09 and [DATE] = 2023-08) , I would like that my table visual didn't display the line 3, this is, the line that has the [9. WBS] = blank() for the [PROJ_CODE] = "123", beacuse I have [9. WBS] values for this [PROJ_CODE] that are different than blank().
For [PROJ_CODE] = "456", I only have 1 value for [9. WBS] - "ZXY". So, for this date selection ([DATE] = 2023-09 and [DATE] = 2023-08) I want this line to maintain in my table visual beacuse the column [9. WBS] only has 1 value for this one [PROJ_CODE].
For [PROJ_CODE] = "980", I only have 1 value for [9. WBS] - blank(). So, for this date selection ([DATE] = 2023-09 and [DATE] = 2023-08) I want this line to maintain in my table visual beacuse, althought the [9. WBS] value is balnk(), this column only has 1 value for this one [PROJ_CODE].
Following the prior example, let's imagine I had just one date selected in my Date Slicer, [DATE] = 2023-09, and I had the following lines for the same [PROJ_CODE] presented before:
line 1: "HUB GER", "GER 12", blank(), "123"
line 2: "HUB GER", "GER 13", "ZXY", "456"
line 3: "HUB GER", "GER 15", blank(), "980"
Consider that there are no more lines for the [PROJ_CODE] values mentioned for [DATE] = 2023-09.
Following the previous logic, I wanted my visual table to still display all of this lines.
For line 1 and 3, although I have [9. WBS] = blank() for each [PROJ_CODE], beacuse there weren't any more [9. WBS] values for the [PROJ_CODE] = "123" and for the [PROJ_CODE] = "980", for this date selection ([DATE] = 2023-09), I wan't this lines to still be displayed.
For the line 2, this is, for [PROJ_CODE] = "456", I only have 1 value for [9. WBS] - "ZXY". So, for this date selection ([DATE] = 2023-09) I want this line to maintain in my table visual beacuse the column [9. WBS] only has 1 value for this one [PROJ_CODE].
I have created two measures to achieved my desired result:
Duplicate No Blank Rule (measure) =
VAR CurrentProjCode = MAX('Report 9'[PROJ_CODE])
RETURN
IF(
ISBLANK(CurrentProjCode),
BLANK(),
CALCULATE(
COUNTROWS(
FILTER(
SUMMARIZE(
'Report 9',
'Report 9'[PROJ_CODE],
'Report 9'[9. WBS]
),
'Report 9'[9. WBS] <> BLANK() &&
'Report 9'[PROJ_CODE] = CurrentProjCode
)
),
ALL('Report 9')
)
)
No Duplicates (measure) =
IF(
([Duplicate No Blank Rule (measure)] >=1 &&
MAX([9. WBS])=BLANK()),
0, 1
)
This last measure I used as a filter in my table visual where it only displayed values where No Duplicates (measure) = 1.
The problem is, I want this measure to affect other visuals as well (guage chart, zebra bi line chart and other tables that don't display the columns [9. WBS] and [PROJ_CODE] - the columns I need for the measure to work).
So, I think the solution is to create a calculated column instead. But beacuse a calculated column is static, it will not perform as my measure, this is, the Date Slicer will not affect the values directly.
Can you help me achieve this by creating calculated columns?
