Forum Discussion

Regular Visitor

6 years ago

Solved

Please help on data model

I am newbie in Power BI and need to build a Power BI report from a table. But I don't have any idea how to build the data model. Please help me!

for example below table is a source table which has member information.

I need to create a report like below format. The second column displays, by end of 2018, the total amount of members who lived in each city. The other column shows their movement, by end of 2019.

Please suggest how to build the data model, Thank you in advance!

Need Help

v-gizhi-msft

6 years ago

Hi,

For your requirement, i add some data to original sample table:

Add an index column and create a rank column as the same with my last post.

Create a What If parameter:

Then try these two measures:

End of Select Year = 
var EndOf2018 = DATE(SELECTEDVALUE('Year Slicer'[Year Slicer]),12,31)
var a = IF(MAX('Table'[Column])=MAXX(FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])),'Table'[Column]),1,0)
var b = IF(CALCULATE(MAX('Table'[RecordEnd]),FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])&&a=1))<EndOf2018,0,1)
return
IF(a=1&&b=0,MAX('Table'[City]),IF(MAX('Table'[RecordStart])<=EndOf2018&&MAX('Table'[RecordEnd])>=EndOf2018,MAX('Table'[City]),0))

End of Following Year = 
var EndOf2019 = DATE(SELECTEDVALUE('Year Slicer'[Year Slicer])+1,12,31)
var a = IF(MAX('Table'[Column])=MAXX(FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])),'Table'[Column]),1,0)
var b = IF(CALCULATE(MAX('Table'[RecordEnd]),FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])&&a=1))<EndOf2019,0,1)
return
IF(a=1&&b=0,MAX('Table'[City]),IF(MAX('Table'[RecordStart])<=EndOf2019&&MAX('Table'[RecordEnd])>=EndOf2019,MAX('Table'[City]),0))

Then try this measure to calculate the amount of member before the end of select year:

Select Year Amount of Member = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),IF(CALCULATE([End of Select Year])<>0,1,0)),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),IF(CALCULATE([End of Select Year])<>0,1,0)))

Try these four measures to calculate the amount in each city before the end of following year:

Following Year in A = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="A",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="A",1,0))))

Following Year in B = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="B",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="B",1,0))))

Following Year in C = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="C",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="C",1,0))))

Following Year in D = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="D",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="D",1,0))))

Choose [City] and above five measures as a table visual, when select one year in slicer, the result shows:

Here is my changed pbix file:

pbix

Hope this helps.

Best Regards,

Giotto Zhi

5 Replies

amitchandak
Super User
6 years ago
The output format is not visible. But you refer to my blog on a similar line.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Greg_Deckler
Community Champion
6 years ago
No image, but you most likely want Open Tickets: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

v-gizhi-msft

Community Support

6 years ago

Hi,

According to your description, i create sample to test:

For your requirement 1:

Please try this calculated column:

Amount of Member = 
var EndOf2018 = DATE(2018,12,31)
return
IF(EndOf2018>='Table'[RecordStart],1,0)

Choose [City] and this column as a table visual, the result shows:

For your reqiurement 2:

Please try to add an index column to original table in Query Editor, then create a calculated column:

Column = CALCULATE(DISTINCTCOUNT('Table'[Index]),'Table','Table'[Member ID]=EARLIER('Table'[Member ID]),'Table'[Index]<=EARLIER('Table'[Index]))

Then try this measure:

City Movement = CONCATENATEX(FILTER('Table','Table'[RecordStart]<=DATE(2019,12,31)),[City]," -> ",[Index],ASC)

Choose [Member ID] and this measure as a table visual, the result shows:

Here is my test pbix file:

pbix

Hope this helps.

Best Regards,

Giotto Zhi

Linda320

Regular Visitor

6 years ago

Thanks for your help, Giotto!

The requirement is that user can choose a year, then the power bi report will show in that year, how many members live in each city A,B,C,D. In the meanwhile, the report will automatically display in the following year, among the members in a specific city, how many still lived in the same city and how many moved to other different cities.

for example,

2018 A: 200 members; 2019: 150 of 200 in A; 30 of 200 in B; 20 of 200 in C

Thanks again!

v-gizhi-msft

Community Support

6 years ago

Hi,

For your requirement, i add some data to original sample table:

Add an index column and create a rank column as the same with my last post.

Create a What If parameter:

Then try these two measures:

End of Select Year = 
var EndOf2018 = DATE(SELECTEDVALUE('Year Slicer'[Year Slicer]),12,31)
var a = IF(MAX('Table'[Column])=MAXX(FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])),'Table'[Column]),1,0)
var b = IF(CALCULATE(MAX('Table'[RecordEnd]),FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])&&a=1))<EndOf2018,0,1)
return
IF(a=1&&b=0,MAX('Table'[City]),IF(MAX('Table'[RecordStart])<=EndOf2018&&MAX('Table'[RecordEnd])>=EndOf2018,MAX('Table'[City]),0))

End of Following Year = 
var EndOf2019 = DATE(SELECTEDVALUE('Year Slicer'[Year Slicer])+1,12,31)
var a = IF(MAX('Table'[Column])=MAXX(FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])),'Table'[Column]),1,0)
var b = IF(CALCULATE(MAX('Table'[RecordEnd]),FILTER(ALLSELECTED('Table'),'Table'[Member ID] in FILTERS('Table'[Member ID])&&a=1))<EndOf2019,0,1)
return
IF(a=1&&b=0,MAX('Table'[City]),IF(MAX('Table'[RecordStart])<=EndOf2019&&MAX('Table'[RecordEnd])>=EndOf2019,MAX('Table'[City]),0))

Then try this measure to calculate the amount of member before the end of select year:

Select Year Amount of Member = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),IF(CALCULATE([End of Select Year])<>0,1,0)),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),IF(CALCULATE([End of Select Year])<>0,1,0)))

Try these four measures to calculate the amount in each city before the end of following year:

Following Year in A = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="A",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="A",1,0))))

Following Year in B = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="B",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="B",1,0))))

Following Year in C = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="C",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="C",1,0))))

Following Year in D = IF(ISINSCOPE('Table'[City]),SUMX(DISTINCT('Table'[Member ID]),CALCULATE(IF([End of Following Year]="D",1,0))),SUMX(GROUPBY('Table','Table'[City],'Table'[Member ID]),CALCULATE(IF([End of Following Year]="D",1,0))))

Choose [City] and above five measures as a table visual, when select one year in slicer, the result shows:

Here is my changed pbix file:

pbix

Hope this helps.

Best Regards,

Giotto Zhi

Forum Discussion

Please help on data model

5 Replies

Related content

Help Please

Modelling

Data Modeling

ML Model - SARIMAX

Cannot Load Model