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.
I have a PBI report which data source is MS Lists.
It is about auditing some criterias.
In the beggining of the month I have 5 columns ( PASSED, FAILED, INCONCLUSIVE, NOT APPLICABLE, NOT CHECKED).
But in the end of the month the column NOT CHECKED no longer exists as all audits have been checked.
Because of that I get a refresh error in PBI Service:
Data source error: The '<pii>NOT CHECKED</pii>' column does not exist in the rowset. Table: AuditsAll.
How can I avoid this?
It is my DAX formula:
RESULT =
IF (
AuditsAll[PASSED] + AuditsAll[FAILED] + AuditsAll[INCONCLUSIVE] + AuditsAll[NOT APPLICABLE] = 0,
"NOT AUDITED",
IF (
AuditsAll[FAILED] > 0,
"FAILED",
IF (
AuditsAll[INCONCLUSIVE] > 0,
"INCONCLUSIVE",
IF ( AuditsAll[NOT CHECKED] > 0, "IN PROGRESS", "PASSED" )
)
)
)
Hi @Anonymous ,
I'm not very clear how your data source operates. When the NOT CHECKED column has no value, could you add a space " " or "null"?
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Try unpivoting the columns PASSED, FAILED, INCONCLUSIVE, NOT APPLICABLE, NOT CHECKED in Power Query.
Using = Table.UnpivotOtherColumns, should the NOT CHECKED column go missing, it won't cause a query error.
We'll call the number of audits for each "Value" and the field value (PASSED, FAILED,...) "Status" for now.
Now,
RESULT =
IF (
CALCULATE(
SUM(AuditsAll[Value]),
KEEPFILTERS(AuditsAll[Status] <> "NOT CHECKED")
)= 0,
"NOT AUDITED",
IF (
CALCULATE(
SUM(AuditsAll[Value]),
KEEPFILTERS(AuditsAll[Status] = "FAILED")
) > 0,
"FAILED",
IF (
CALCULATE(
SUM(AuditsAll[Value]),
KEEPFILTERS(AuditsAll[Status] = "INCONCLUSIVE")
) > 0,
"INCONCLUSIVE",
IF ( CALCULATE(
SUM(AuditsAll[Value]),
KEEPFILTERS(AuditsAll[Status] = "NOT CHECKED")
) > 0, "IN PROGRESS", "PASSED" )
)
)
)
Because "Not checked" is just another possible value of [Status], nothing would occour if it's missing.
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 |
---|---|
47 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |