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.
Hi,
my datamodel contains two tables. First table is "Jobs" with an "Attributes" filed:
Second table is "Attribute" with a "Name" filed.
There is no connection between these tables. Important thing: The Jobs[Attributes] field does not contain the exact same values like Attribute[Name]. For example, the Attribute table has the value "Christmas", the Jobs table has values like "Christmas 2017", "Christmas 2018", etc.
What i need is the amount of jobs for each attribute. For the sample data the result should look like this:
I tried a calculated column on the Attribute table, but I only got an error:
AmountJobs = CALCULATE(COUNTROWS(Jobs); SEARCH(Attribute[Name]; Jobs[Attributes]))
Solved! Go to Solution.
this will work
Measure = VAR _Attribute = SELECTEDVALUE(Attribute[Name],BLANK()) VAR _SearchFlag = ADDCOLUMNS(Jobs,"Flag",SEARCH(_Attribute,Jobs[Attributes],1,BLANK())) VAR _RelevantRows = FILTER(_SearchFlag,[Flag]<>BLANK()) RETURN COUNTROWS(_RelevantRows)
you need to use Name from Attribute table and measure in the visual, there should be no joins between the tables
it does the job, but search will affect the performance badly (it has to iterate every string in the Jobs table), so the performance may not be great for bigger datasets
this will work
Measure = VAR _Attribute = SELECTEDVALUE(Attribute[Name],BLANK()) VAR _SearchFlag = ADDCOLUMNS(Jobs,"Flag",SEARCH(_Attribute,Jobs[Attributes],1,BLANK())) VAR _RelevantRows = FILTER(_SearchFlag,[Flag]<>BLANK()) RETURN COUNTROWS(_RelevantRows)
you need to use Name from Attribute table and measure in the visual, there should be no joins between the tables
it does the job, but search will affect the performance badly (it has to iterate every string in the Jobs table), so the performance may not be great for bigger datasets
Thank you for your fast solution, this works well.
Would it be better for the performance when the amount of jobs is a calculated column in the Attribute table?
does it perform badly now? if you're fine with performance then I would keep it as is
The problem with precalculating columns in the table is following: you would have to do it for each event, so Christmas, New Year, and Easter separately, and additional one for every single new event. You cannot have calculated column that's dependant on the slicer value, although it does work nicely as variable (cause it's always single value in the filter context of the visual, i.e. for Christmas column in the chart I only calculate Christmas)
I think the most performant solution would be changing the Jobs table to something like this:
ID | Attribute | Year |
1 | Christmas | 2017 |
2 | Christmas | 2017 |
2 | New Year | 2017 |
3 | New Year | 2018 |
3 | Easter | 2018 |
then you can just do simple row count of unique IDs per Attributes
transformation could be done in M, assuming the naming conventions is always "eventname YYYY"
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |