Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all, I'm currently trying to make a few measures for my data model (for simplicity's sake, it's basically a fact table linked to a calendar via a foreign key).
However, a measure I'm making doesn't work, and returns this error :
When I tried looking into why that may be, it seems that there are some blank values in a dimension :
But when I look up the source view for it, there doesn't seem to be any :
I thought maybe there were some fact values that weren't linked to the dimension, but all foreign keys do exist in it :
When trying to reproduce the measure in PBI, I see that there is a blank value involved :
Is there any way to know how to fix it ? Here's what the measure looks like :
MyMeasure:=
VAR chosen_date =
IF (
ISCROSSFILTERED ( '0-05 - Calendar' ),
LASTDATE ( '0-05 - Calendar'[Date - Civil] ),
TODAY ()
)
RETURN
CALCULATE (
SUM ( 'Fact'[Quantity] ),
filter(all(Fact),Fact[Prediction_Date]
= VALUE ( FORMAT ( EDATE ( chosen_date, -3 ), "YYYYMM" ) )&&
Fact[Month_In_Year] = VALUE ( FORMAT ( chosen_date, "YYYYMM" ) )
))
Thanks in advance,
Nossair
Solved! Go to Solution.
@Anonymous , I doubt you fact has some dates which are missing in the date/year table
refer
https://www.youtube.com/watch?v=23qemkDqMVY
@Anonymous , I doubt you fact has some dates which are missing in the date/year table
refer
https://www.youtube.com/watch?v=23qemkDqMVY
You're right ! I tried to simplify the model but I just realized that there was another fact table in my model that had a calendar foreign key that wasn't in the dimension. Thanks a lot !
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |