Please help on data model

[Linda320]

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!

[v-gizhi-msft]

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]

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 · Accepted solution]

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

[Greg_Deckler]

No image, but you most likely want Open Tickets: https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

[amitchandak]

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