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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
abukapsoun
Post Patron
Post Patron

Calculated Table

Hi Dears,

 

I have the following Table

I have the following formula

Count X = CALCULATE(COUNT('table1'[Value1]),FILTER(table1','table1'[Value1]="X"))

 

How can I create a new table, that return in Column1 the Period (P1, P2, ...) and Column2, the value of (Count X P3 - Count X P2)

so P3 will be (Count X P3 - Count X P2) and so on 

 

1.PNG

 

 

1 ACCEPTED SOLUTION

This probably isn't the cleanest way (I was trying to do it in one DAX expression initially), but you can create a summary table and then add a calculated column on top of it.

 

First step would be to summarize your data table by Period and Count of X.  I went ahead and added an "Index" column - this assumes that your periods will always be incrementing (P1, P2, ... P5, P6, .. Pn).

 

SummaryTable = SUMMARIZE(
Table1,
Table1[Period],
"Index", MID( Table1[Period], 2, LEN( MAX(Table1[Period]) ) ),
"Count of X", CALCULATE( COUNTROWS(Table1), FILTER(Table1, Table1[Value] = "X"))
)

This will give us a table that looks like:

 

Screen Shot 2017-09-22 at 11.26.39 AM.png

 

From here, we can add a "Difference" column using the following formula:

 

Difference = [Count of X] - LOOKUPVALUE('Table'[Count of X], 'Table'[Index], 'Table'[Index] + 1)

Screen Shot 2017-09-22 at 11.29.08 AM.png

 

Note: If you receive an error on this last step, you need to change the Data Type for the [Index] column to a Whole Number instead of Text.

Dan Malagari
Consultant at Headspring

View solution in original post

4 REPLIES 4
malagari
Responsive Resident
Responsive Resident

Can you show what you want the resulting table to look like? I'm having a hard time following your explanation.

 

It sounds like your case would be a good use of the SUMMARIZE function.  You can group by the Period, and then define your calculated columns with whatever formula you please (this is where I was lost in your explanation).

 

Screen Shot 2017-09-22 at 10.35.42 AM.png

 

Dan Malagari
Consultant at Headspring

@malagari

Thanks for your support, In fact I want to generate 2 columns, 

Period             Difference

P1                    Count of X in P1 - Count of X in P2

P2                    Count of X in P2 - Count of X in P3

P3                    Count of X in P4 - Count of X in P5

P4                    Count of X in P5 - Count of X in P6

 

I want to generate how much we had an increment in X count between the periods. 

 

This probably isn't the cleanest way (I was trying to do it in one DAX expression initially), but you can create a summary table and then add a calculated column on top of it.

 

First step would be to summarize your data table by Period and Count of X.  I went ahead and added an "Index" column - this assumes that your periods will always be incrementing (P1, P2, ... P5, P6, .. Pn).

 

SummaryTable = SUMMARIZE(
Table1,
Table1[Period],
"Index", MID( Table1[Period], 2, LEN( MAX(Table1[Period]) ) ),
"Count of X", CALCULATE( COUNTROWS(Table1), FILTER(Table1, Table1[Value] = "X"))
)

This will give us a table that looks like:

 

Screen Shot 2017-09-22 at 11.26.39 AM.png

 

From here, we can add a "Difference" column using the following formula:

 

Difference = [Count of X] - LOOKUPVALUE('Table'[Count of X], 'Table'[Index], 'Table'[Index] + 1)

Screen Shot 2017-09-22 at 11.29.08 AM.png

 

Note: If you receive an error on this last step, you need to change the Data Type for the [Index] column to a Whole Number instead of Text.

Dan Malagari
Consultant at Headspring

@malagari

 

Thank you! It was exactly what I need.

One last thing please, what is the logic of that lockupvalue function?

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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