Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Context:
I am attempting to create a switch YTD column that selects either a Rolling YTD (From the first available amount onward) or a Non Rolling YTD (Regular YTD). The switch is dependent on a flag, if it's Y then use the Rolling YTD, if it's N then use the Non Rolling YTD. The issue I am running into is that due to the gaps between my month year periods for certain IDs, thus I am returning Blanks in my switch column when I select/filter the specific month year in which the ID does not exist.
Question:
How do I pull in the correct YTD(measure) based on this flag that technically doesn't exist within a selected month year period? My "switch" column is named Total YTD.
Side Note: The Flag Column is consistent across dates.
Attempted Solution That Doesn't Work For MY Case:
Current Attempt Sample:
Measures:
Solved! Go to Solution.
Created column yearmonth as a date, using the first of the month for the day. Then created Last Remark measure which provides you a Rolling YTD Flag that is not blank (on the backend; Original Rolling YTD Flag has conditional formatting on to highlight blank values). The Total YTD measure now references the last remark measure.
Created column yearmonth as a date, using the first of the month for the day. Then created Last Remark measure which provides you a Rolling YTD Flag that is not blank (on the backend; Original Rolling YTD Flag has conditional formatting on to highlight blank values). The Total YTD measure now references the last remark measure.
I suspect the problem is that SELECTEDVALUE is not returning a value for you. Experiment with replacing:
VAR FLAG = SELECTEDVALUE( TB_Sample_Data[Rolling YTD Flag] )
with
VAR FLAG = SELECTEDVALUE( TB_Sample_Data[Rolling YTD Flag] , "Y")
or
VAR FLAG = SELECTEDVALUE( TB_Sample_Data[Rolling YTD Flag] , "N")
These give SELECTEDVALUE a default value when it can't work it out. This may not seem immediately useful as you don't want it to default to Y or N, but will at least tell us where the issue is. If, in your visual, you still have blanks then it must be something else.
If this is the problem, then we need to replace SELECTEDVALUE with something else. Try creating a measure like this:
test = CALCULATE(COUNTROWS('TB_Sample_Data'),TB_Sample_Data[Rolling YTD Flag] = "Y") > 0
This should return True/False instead of Y/N.
If this works, then incorporate this into your [TOTAL YTD] measure, replacing the VAR FLAG definition you currently have.
Testing those Var Flags does fill in the blanks, but the "test" count measures are defaulting to false on the "blank" rows.
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |