Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Best regards!
I have a problem, I hope you can explain me well and you can help me...
In a table I have a list of people (unique ID code), and a column that identifies travel and travel by codes in the same column.
usercode | Code | group |
user1 | barco | Vehicle |
user1 | airplane | Vehicle |
user1 | Madrid | city |
user2 | car | Vehicle |
user2 | texas | city |
user3 | airplane | Vehicle |
user3 | Madrid | city |
user3 | Germany | city |
then, I can count the users who have traveled by car, boat, plane, madrid, texas, but I need:
-segment the users who traveled in which vehicle, to which cities they traveled, that is, count "plane" and those users who traveled by plane to which cities they traveled.
-failing that of the city of Madrid how many traveled by boat and how many by plane.
as the data is in the same column because I can not filter them either with IF, or doing SWITCH
mesure boat =
CALCULATE(
COUNT(usercode] record),
registrationtray[code] = "boat"
recordtray[group] = "city"
)
the idea of this is then to make a graph where you put the number of trips per boat and the cities with their respective amount ...
Solved! Go to Solution.
Hi @Syndicate_Admin ,
According to your description, here’s my solution.
For example calculating the count of users to Madrid by boat:
1.Create a measure to check the user to Madrid by boat.
Flag =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[User code] ),
[Code] = "boat"
|| [Code] = "Madrid"
)
)
2.Create another measure to count the user to Madrid by boat.
UserCount to Madrid by boat = CALCULATE(DISTINCTCOUNT('Table'[User code]),FILTER('Table',[Flag]=2))
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Syndicate_Admin ,
According to your description, here’s my solution.
For example calculating the count of users to Madrid by boat:
1.Create a measure to check the user to Madrid by boat.
Flag =
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[User code] ),
[Code] = "boat"
|| [Code] = "Madrid"
)
)
2.Create another measure to count the user to Madrid by boat.
UserCount to Madrid by boat = CALCULATE(DISTINCTCOUNT('Table'[User code]),FILTER('Table',[Flag]=2))
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Your data is not in a usable format. Transfom it like this
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKi1OLTJU0lFKSixKzgfSYakZmck5qUqxOgi5xMyigpzEvFQc0r6JKUWZKUBGcmZJJVzGCCSQWIRFD0imJLUisRhdizF+u4xx2gWScU8tyk3Mq4RLxQIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [usercode = _t, Code = _t, group = _t]),
#"Filter Vehicle" = Table.SelectRows(Source, each ([group] = "Vehicle")),
#"Filter City" = Table.SelectRows(Source, each ([group] = "city")),
#"Merged Queries" = Table.NestedJoin(#"Filter Vehicle", {"usercode"}, #"Filter City", {"usercode"}, "Table (2)", JoinKind.FullOuter),
#"Renamed Columns" = Table.RenameColumns(#"Merged Queries",{{"Code", "Vehicle"}}),
#"Expanded Table (2)" = Table.ExpandTableColumn(#"Renamed Columns", "Table (2)", {"Code"}, {"Code"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Table (2)",{{"Code", "City"}}),
#"Removed Other Columns" = Table.SelectColumns(#"Renamed Columns1",{"usercode", "Vehicle", "City"})
in
#"Removed Other Columns"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Note: Germany is not a city.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |