cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dalet22
New Member

Should I use VAR in this case?

Hi all,

 

I have a quick question. 

I have a string value called con_number, and string value called Year. I am trying to understand the unique con_number by year. What I did was creating a new measure called distinct_con: distinctcount(con_number), then I created a table dragging discint_con and year in. However what I noticed was: it isn't grouped by as expected (Please see screenshot I attached here) 

I did some research and it seems like VAR will be needed for this situation. I am still very new to PowerBI and would appreciate if any of you can share some knowledge on this issue. Thanks!
Capture.PNG

1 ACCEPTED SOLUTION

OK. That makes sense then. The arrow indicates that CU_Info filters Sessions (but not the reverse).

 

In your visual, you have Sesssions[Year] but since the relationship filtering doesn't flow from Sessions to CU_Info (it goes the other way around), it's counting all of the Con_number values for each year.

 

There are a variety of solutions to this, but the best thing to do in the long run is to make sure you have a proper star schema set up with relevant dimension tables, including a date table. You can usually write DAX to manipulate filters to force things to work but getting the model right makes life much easier down the road (albeit at the cost of having the more upfront cost of building your data model).

 

For this particular case, I think you could write a measure like this to apply the filtering you want using Sessions as a table filter:

distinct_con = CALCULATE ( DISTINCTCOUNT ( CU_Info[con_number] ), Sessions )

Should work in a pinch. 

View solution in original post

8 REPLIES 8
AlexisOlson
Super User
Super User

I'm guessing Year and con_number are not on the same table. Is that correct? If so, you need some way to know which con_number are associated with which years. This is usually done via relationships between tables.

Hi Alexis, thanks for your reply. Yes they're from two tables. Year is from Session table and Con_number from Info table. I was connecting those two tables using con_number. 

Do you have any suggestions how I can make it work? Thank you!

It depends on how the tables are related. Can you screenshot the relationship diagram?

Sure, attaching the screenshot here (highlighted is how I joined 2 tables) Thanks so much in advance!

 Capture.PNG

OK. That makes sense then. The arrow indicates that CU_Info filters Sessions (but not the reverse).

 

In your visual, you have Sesssions[Year] but since the relationship filtering doesn't flow from Sessions to CU_Info (it goes the other way around), it's counting all of the Con_number values for each year.

 

There are a variety of solutions to this, but the best thing to do in the long run is to make sure you have a proper star schema set up with relevant dimension tables, including a date table. You can usually write DAX to manipulate filters to force things to work but getting the model right makes life much easier down the road (albeit at the cost of having the more upfront cost of building your data model).

 

For this particular case, I think you could write a measure like this to apply the filtering you want using Sessions as a table filter:

distinct_con = CALCULATE ( DISTINCTCOUNT ( CU_Info[con_number] ), Sessions )

Should work in a pinch. 

View solution in original post

Thanks so much! I was testing using values from those 2 tables to do some group bys, and what you shared answered all my questions!

 

I tried your formula to create a new measure, the formula looks perfect to me but I got this error: couldn't load the data for this visual. We couldn't fold the expression to the data source. Please try a simpler expression. 

It looks like a direct query error message to me, happened before. 

Yeah, DirectQuery is much more limited. You could try variations of what I suggested but I haven't written DAX for DirectQuery very much and this might not work either.

distinct_con =
CALCULATE (
    DISTINCTCOUNT ( CU_Info[con_number] ),
    TREATAS (
        VALUES ( Sessions[customDimensions_ContractNumber] ),
        CU_Info[ContractNumber]
    )
)

Thanks Alexis! I tried one of your other solutions too: change the filter from single to both. That actually worked very well, numbers aligned with old records. Thank you! Have to do more research to understand the schema in PowerBI. 

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors