Forum Discussion
If same column has multiple values in another column
Hello,
I have Act ID number and Repair type. Same Act ID number can have multiple Repair types (total of 9 repair types exists).
Usually it has same repair types (its a train maintenance and Act ID depends on quantity of wagons).
But there are few exceptions where same Act ID, have different repairs, in example below Act ID 7139, had one ER-1 repair and two TP-3 repairs, and I want this Act not to be visible in my time calculations and main report.
Thank you for your help!
Anonymous , You can create a new column that can act as flag to filter
if(calculate(distinctcount(Table[repair type]), filter(table, [Act ID] =earlier([Act id])))=1, true(), false())
or a measure like
sumx(filter(summarize(Table, Table[Act ID], "_1", distinctcount(Table[repair type]), "_2", count(Table[Act ID])), [_1] =1),[_2])
4 Replies
Anonymous , what is the expected output, can you please provide the same.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
amitchandakunfortunatelly I dont think I can share pbix.
I want to make report where would be visible how much hours were used on specific repair type (column chart), actual hours worked vs norms (line), where norms are fixed hours depending on repair type.
But I want to remove values, where same Act ID has different repair types, because these are exceptions and distorts results.
E.g. TP-3 fixed rate is 9 hours, where ER-1 fixed rate is 18 hours, as its train we cant set TP-3 finished earlier as one wagon cant be released separatly, and there are also other staturary reasons.
Anonymous , You can create a new column that can act as flag to filter
if(calculate(distinctcount(Table[repair type]), filter(table, [Act ID] =earlier([Act id])))=1, true(), false())
or a measure like
sumx(filter(summarize(Table, Table[Act ID], "_1", distinctcount(Table[repair type]), "_2", count(Table[Act ID])), [_1] =1),[_2])