Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
BIswajit_Das
Solution Supplier
Solution Supplier

Calculated Column

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

slnocount
a10
b20
c30
d40
e12

when i select value 2 from table1 
the calculated column on table should return like

slnocountres
a1012
b2022
c3032
d4042
e12

14

and when i select value 4
the result should be

slnocountres
a1014
b2024
c3034
d4044
e12

16

Thanks & Regards..

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

Animation37.gif

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.

View solution in original post

8 REPLIES 8
BIswajit_Das
Solution Supplier
Solution Supplier

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....

Anonymous
Not applicable

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

Animation37.gif

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

BIswajit_Das_0-1701761320862.gif

 

Not Working on this count measure

Anonymous
Not applicable

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

Anonymous
Not applicable

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 )

Animation38.gif

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.

Anonymous
Not applicable

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.

swikritee_p
Resolver II
Resolver II

@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]) 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.