Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

Hi all,

 

I have a fact table with these columns:

  • Ticket ID (INCIDENT_NUMBER)
  • effective start date (DATA_INIZIO_COMPETENZA)
  • effective end date (DATA_FINE_COMPETENZA)

I'd like to build a chart that count (distinct) ticket ID valid at the calendar date (last 30 days).

 

Using CALCULATE, DISTINCT COUNT received the error in the Subject

 

Expression:

 

BacklogToday =CALCULATE(DISTINCTCOUNT(HPD_HELP_DESK_HIS_T[INCIDENT_NUMBER]);ISBLANK(HPD_HELP_DESK_HIS_T[DATA_FINE_COMPETENZA]) || HPD_HELP_DESK_HIS_T[DATA_FINE_COMPETENZA] > osservazione;HPD_HELP_DESK_HIS_T[DATA_INIZIO_COMPETENZA]<=osservazione)
 
"Osservazione" is a table generate with CALENDARAUTO(12).
OSSERVAZIONE = CALENDARauto(12)
 
Can u help me?
Thanks a lot
2 REPLIES 2
Greg_Deckler
Super User
Super User

Right, so you are comparing a column from one table to an entire table so that's not going to work. You would need to compare to a single filtered value in that column. Perhaps we should back up, what are you trying to accomplish?

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Thank you. I'm trying to create a chart that shows the history of valid tickets (the progress of this count over time).

On the x-axis, I have the calendar; on y-axis I have the count of valid tickets on that date.

 

I was able to calculate the value on the single date (below expression is running), but I can't handle the observation date as a variable.

 

BacklogToday = CALCULATE(DISTINCTCOUNT(HPD_HELP_DESK_HIS_T[INCIDENT_NUMBER]);ISBLANK(HPD_HELP_DESK_HIS_T[DATA_FINE_COMPETENZA]) || HPD_HELP_DESK_HIS_T[DATA_FINE_COMPETENZA] > TODAY();HPD_HELP_DESK_HIS_T[DATA_INIZIO_COMPETENZA]<=TODAY())

 

 
How can I make the date a variable (time)?  And so look how the volume changes over time?
 
Thanks again!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors