Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
Want to create a calculated column which calculate values as per the selected column value from another table
i.e
table1
sub |
1 |
2 |
3 |
4 |
5 |
table2
slno | count |
a | 10 |
b | 20 |
c | 30 |
d | 40 |
e | 12 |
when i select value 2 from table1
the calculated column on table should return like
slno | count | res |
a | 10 | 12 |
b | 20 | 22 |
c | 30 | 32 |
d | 40 | 42 |
e | 12 | 14 |
and when i select value 4
the result should be
slno | count | res |
a | 10 | 14 |
b | 20 | 24 |
c | 30 | 34 |
d | 40 | 44 |
e | 12 | 16 |
Thanks & Regards..
Solved! Go to Solution.
Hi @BIswajit_Das ,
Please try to create measure with below dax formula:
Res =
VAR _a =
SELECTEDVALUE ( Table1[Sub] )
VAR _b =
SELECTEDVALUE ( Table2[Count] )
RETURN
_a + _b
Measure =
VAR tmp =
CALCULATETABLE (
VALUES ( Table2[Slno] ),
FILTER ( ALL ( Table2 ), [Res] > 25 )
)
VAR _a =
COUNTROWS ( tmp )
RETURN
_a
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hello @Anonymous and @swikritee_p
I have created measure as per my requirement but there's another issue there
i.e
i want to
calculate(
distinctcount(table2[slno]),
filter(new_meausre > 25)
)
But its not working cause of measure
Is there any way to do so
without creating calculated columns.
Thanks & Regards....
Hi @BIswajit_Das ,
Please try to create measure with below dax formula:
Res =
VAR _a =
SELECTEDVALUE ( Table1[Sub] )
VAR _b =
SELECTEDVALUE ( Table2[Count] )
RETURN
_a + _b
Measure =
VAR tmp =
CALCULATETABLE (
VALUES ( Table2[Slno] ),
FILTER ( ALL ( Table2 ), [Res] > 25 )
)
VAR _a =
COUNTROWS ( tmp )
RETURN
_a
Please refer the attached .pbix file.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Measure = VAR tmp = CALCULATETABLE ( VALUES ( Table2[Slno] ), FILTER ( ALL ( Table2 ), [Res] > 25 ) ) VAR _a = COUNTROWS ( tmp ) RETURN _a
Not Working on this count measure
Hi @BIswajit_Das ,
The measure work correctly.
According to the formula, it will only count the rows that when the 'Res' measure value bigger than 25, but for the test data whenever you choose any value in the slicer, only two rows of data satisfy the condition.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Let's take an example
i.e
res results are in
15
20
25
27
29
check for divided by 5 values on th column
Hi @BIswajit_Das ,
Only need modify the dax formula:
Measure =
VAR tmp =
CALCULATETABLE (
VALUES ( Table2[Slno] ),
FILTER ( ALL ( Table2 ), DIVIDE ( [Res], 5 ) > 25 )
)
VAR _a =
COUNTROWS ( tmp )
RETURN
IF ( ISBLANK ( _a ), 0, _a )
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @BIswajit_Das ,
Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please feel free to let me know.
Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@BIswajit_Das , You can create measure not calculated column based on selected value.
Create a new measure
New Measure = Sum(Table2[count]) + selectedvalue(Table1[Sub])
Alternative
New Measure = Sum(Table2[count]) + maxx(allselected(Table1) , Table1[Sub])
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
111 | |
108 | |
108 | |
93 | |
61 |
User | Count |
---|---|
169 | |
138 | |
135 | |
102 | |
86 |