topic Re: I start month with 5 columns but in the end of the month one column does not exist in the rowset. in DAX Commands and Tips

I start month with 5 columns but in the end of the month one column does not exist in the rowset.

Anonymous — Tue, 10 May 2022 07:32:29 GMT

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

Re: I start month with 5 columns but in the end of the month one column does not exist in the rowset.

rbriga — Tue, 10 May 2022 09:32:08 GMT

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.

Re: I start month with 5 columns but in the end of the month one column does not exist in the rowset.

v-yanjiang-msft — Fri, 13 May 2022 09:19:45 GMT

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.