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.
Goodday,
Im hoping someone can help me out on this one.
I have a table where ALL the incidents are registered and categorized based on 'Soort Schade' and a few extra colums.
What i want to do is make a new table and a colum that lists all the unique values of the column 'Infrastructuur' and then counts all the times that value is in that column
So basically i now have:
Damage Infrastructuur
1. Electric
2. Gas
3. Water
4. Water
and i want a new table that combines that data into:
Infrastructuur Number of tickets
Electric 1
Gas 1
Water 2
then i want to calculate other stuff but that is for later 🙂
Solved! Go to Solution.
Hi @RvdHeijden,
In this scenario, you can adjust the formula like below.
New Table = SUMMARIZE ( Data, Data[Infrastructuur], "Civiel (BIS)", COUNTROWS ( FILTER ( Data, Data[TeamType] = "Civiel (BIS)" ) ), "Civiel (Tuin)", COUNTROWS ( FILTER ( Data, Data[TeamType] = "Civiel (Tuin)" ) ) )
Regards
Hi @RvdHeijden,
The formula(DAX) below should also work.
New Table = SUMMARIZE ( Data, Data[Infrastructuur], "Number Of Tickets", COUNTROWS ( Data ) )
Regards
it looks like i need to change my question a bit because i wasn't clear.
in both you formulas you added the colum 'Number of Tickets' but that is not a column.
my table where the 'Infrastructuur' column is in just lists the types, the new table should 'calculate' the number of times a type of Infrastructuur is in the table
Besides the column 'infrastructure' i would like to add more colums such as 'teamtype' or whatever how do i add this to the formula ?
so basically
infrastructuur TeamType
gas Civiel (BIS)
electric Civiel (BIS)
water Civiel (Tuin)
water Civiel (BIS)
water Civiel (Tuin)
electric Civiel (Tuin)
the new table should calculate the times 'water' is in the other table to return the result
Civiel (BIS) Civiel (Tuin)
gas 1
electric 1 1
water 1 2
Hi @RvdHeijden,
In this scenario, you can adjust the formula like below.
New Table = SUMMARIZE ( Data, Data[Infrastructuur], "Civiel (BIS)", COUNTROWS ( FILTER ( Data, Data[TeamType] = "Civiel (BIS)" ) ), "Civiel (Tuin)", COUNTROWS ( FILTER ( Data, Data[TeamType] = "Civiel (Tuin)" ) ) )
Regards
So basically you want to summarize this table :
Damage Infrastructuur
1. Electric
2. Gas
3. Water
4. Water
Let's assume the table above is called 'Data'.
If the 'Data' table comes from a DAX query, then it won't be available in Power Query and you need to use a DAX method:
Use this DAX query to create your desired table ('Modeling' tab and then 'New Table' in Power BI Desktop):
New Table = ADDCOLUMNS( SUMMARIZE(Data;Data[Infrastructuur]);"Number Of Tickets"; CALCULATE(COUNTROWS(Data) ))
if your 'Data' table is available in Power Query, you can use a the 'Group by' feature and chose count rows in the 'operation' section.
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 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |