Should I use VAR in this case?

[Anonymous]

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!

[AlexisOlson]

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.

[Anonymous]

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!

[AlexisOlson]

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

[Anonymous]

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

 

[AlexisOlson · 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. 

[Anonymous]

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. 

[AlexisOlson]

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

[Anonymous]

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.