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,
I'm new to the Community, so sorry for posting such an easy question (I guess). But I cannot find any solutions online as of yet.
I have three tables. A shows the main units, B shows the categories and C show what categories the units fall into (can be multiple). Like this:
A (Units)
Unit ID | Unit name |
1 | John |
2 | Julie |
3 | Oscar |
B (Categories)
Child ID | Parent ID | Label |
1 | Category One | |
2 | 1 | A |
3 | 1 | B |
4 | 2 | X |
C (Category selections)
Unit ID | Category1 | Category2 | Category3 |
1 | Category One | Category One-B | |
2 | Category One | ||
3 | Category One | Category One-A | Category One-A-X |
I simply want to show statistics of how many units have selected each Category. For example: 3 units have selected Category One and 1 unit have selected Category One-B. I know it would be more presice to have the category ID in table C, but my data is not structured like that at the moment.
My thought was then to make three new columns in table B: The path, the level in the hierarchy and then produce the entire label-structure. Like this:
B (Categories)
Child ID | Parent ID | Label | Path (=PATH(Child ID, Parent ID) | Level (=PATHLENGTH(Path)) | LabelHierarchy |
1 | Category One | 1 | 1 | Category One | |
2 | 1 | A | 1 | 2 | 2 | Category One-A |
3 | 1 | B | 1 | 3 | 2 | Category One-B |
4 | 2 | X | 1 | 2 | 4 | 3 | Category One-A-X |
Label Hierarchy = LOOKUPVALUE(label, child ID, PATHITEM(path, 1,1))&IF(level>1, "-", "") &
LOOKUPVALUE(label, child ID, PATHITEM(path, 2,1))&IF(level>2, "-", "") &
LOOKUPVALUE(label, child ID, PATHITEM(path, 3,1))&IF(level>3, "-", "")
Then I unpivoted table C for the categories. Lastly, when I try to make a relation between Table B[LabelHierarchy] and Table C[Value], I get an error: circular dependency.
What am I doing wrong? Thanx
Solved! Go to Solution.
Hi @Anonymous ,
You need to add 3 columns to your Kategorier table:
Level1 =
VAR LevelNumber = 1
VAR LevelKey = PATHITEM (Kategorier[Path], LevelNumber, INTEGER )
VAR LevelName = LOOKUPVALUE ( Kategorier[Label], Kategorier[Child], LevelKey )
VAR Result = LevelName
RETURN
Result
Level2 =
VAR LevelNumber = 2
VAR LevelKey = PATHITEM (Kategorier[Path], LevelNumber, INTEGER )
VAR LevelName = LOOKUPVALUE ( Kategorier[Label], Kategorier[Child], LevelKey )
VAR Result = LevelName
RETURN
if(ISBLANK( Result), Kategorier[Level1], Result)
Level3 =
VAR LevelNumber = 3
VAR LevelKey = PATHITEM (Kategorier[Path], LevelNumber, INTEGER )
VAR LevelName = LOOKUPVALUE ( Kategorier[Label], Kategorier[Child], LevelKey )
VAR Result = LevelName
RETURN
if(ISBLANK( Result), Kategorier[Level2], Result)
Now adapt your measure to:
Counts =
CALCULATE (
COUNT ( Avvikene[Avvik] ),
CROSSFILTER ( Avvikene[Avvik], Kategorivalg[Avvik], BOTH ),
FILTER (
ALL ( Kategorivalg[Verdi] ),
Kategorivalg[Verdi]
IN VALUES ( Kategorier[Kategori Sti] )
&& Kategorivalg[Verdi] = SELECTEDVALUE ( Kategorivalg[Verdi] )
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you for all the help! I figured it out without making the relationship. Instead I simply repeated the steps in table C (after unpivoting) with the use of LOOKUPVALUE. This is probably not the best way to build the model, but it works. Thank you Miguel for all the help.
Thank you for all the help! I figured it out without making the relationship. Instead I simply repeated the steps in table C (after unpivoting) with the use of LOOKUPVALUE. This is probably not the best way to build the model, but it works. Thank you Miguel for all the help.
Hi @Anonymous ,
Glad you were abble to figure out the answer. Don't forget to mark correct answer to help others.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks again for replying!
The numbers you gave are correct. But I still need to be able to slice these in a hierarchy. Sort of like this:
And as far as I can see, I still need the relationship, because I want to show the name of the category (not the complete path) in the charts I'm producing.
How exactly is the result in column B depending on values in table C? The two tables come from different sources, and all I did with the columns in table B is to create the path and level.
I can't seem to get my head around this. Sorry about that. Greatful for all the help!
Hi @Anonymous ,
You need to add 3 columns to your Kategorier table:
Level1 =
VAR LevelNumber = 1
VAR LevelKey = PATHITEM (Kategorier[Path], LevelNumber, INTEGER )
VAR LevelName = LOOKUPVALUE ( Kategorier[Label], Kategorier[Child], LevelKey )
VAR Result = LevelName
RETURN
Result
Level2 =
VAR LevelNumber = 2
VAR LevelKey = PATHITEM (Kategorier[Path], LevelNumber, INTEGER )
VAR LevelName = LOOKUPVALUE ( Kategorier[Label], Kategorier[Child], LevelKey )
VAR Result = LevelName
RETURN
if(ISBLANK( Result), Kategorier[Level1], Result)
Level3 =
VAR LevelNumber = 3
VAR LevelKey = PATHITEM (Kategorier[Path], LevelNumber, INTEGER )
VAR LevelName = LOOKUPVALUE ( Kategorier[Label], Kategorier[Child], LevelKey )
VAR Result = LevelName
RETURN
if(ISBLANK( Result), Kategorier[Level2], Result)
Now adapt your measure to:
Counts =
CALCULATE (
COUNT ( Avvikene[Avvik] ),
CROSSFILTER ( Avvikene[Avvik], Kategorivalg[Avvik], BOTH ),
FILTER (
ALL ( Kategorivalg[Verdi] ),
Kategorivalg[Verdi]
IN VALUES ( Kategorier[Kategori Sti] )
&& Kategorivalg[Verdi] = SELECTEDVALUE ( Kategorivalg[Verdi] )
)
)
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi, Miguel.
Thank you for your reply. I've tried to share the file here: https://drive.google.com/file/d/1wW9BrOjQC5TVePdFxsNd1Myu4rbwed0V/view?usp=sharing
What columns should be connected if not the label? I cannot see how the two tables are connected through anything else. Sorry for being a complete amateur on this subject.
Kristian
Hi @Anonymous ,
You want to count how many users selected a hierarchy so in this case you would get:
A - 2
A>B - 2
A>C - 1
A>B>D - 1
Is this the case?
If this is what you need you need, the issue is that table 1 is filtering table 3, so you cannot get the count that you want, in this case you need to make the use of the bidiretionality so you just need to make the calculation like this:
Counts = CALCULATE(COUNT(Avvikene[Avvik]), CROSSFILTER(Avvikene[Avvik], Kategorivalg[Avvik], Both))
On the images above you can see the chart that you provided and one with the use of the metric above.
PBIX file attach.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @Anonymous ,
The issue here is that the result on table B is depending on values of table C, this causes the circulary dependency when you are making the relationship.
Have you tried making the relationship without the label column?
This type of calculations based on Parent Child, needs lots of attention to the model setup especially when you star to have several dimensions.
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering 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 |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |