Forum Discussion

Anonymous

Not applicable

5 years ago

Solved

sql to powerbi

Hello,

Can anyone help me to covert the below SQL query to DAX measure?

Select count(distinct id), Org_level from [dbo].[vw_rpg_census]
where status='Active' and is_available=1 group by Org_level

Thank you so much for your help!

amitchandak
5 years ago
Anonymous , Try like

N= calculate(distinctcount(vw_rpg_census[id]) , filter(vw_rpg_census, vw_rpg_census[status]="Active" && vw_rpg_census[is_available]=1))

D =calculate(distinctcount(vw_rpg_census[id]) , filter(allselected(vw_rpg_census), vw_rpg_census[status]="Active" && vw_rpg_census[is_available]=1))

D need a sumx version

D=
Sumx(values(vw_rpg_census[Org_level]),calculate(distinctcount(vw_rpg_census[id]) , filter(allselected(vw_rpg_census), vw_rpg_census[status]="Active" && vw_rpg_census[is_available]=1))
)

New have %

percent= Divide([N],[D])

3 Replies

amitchandak
Super User
5 years ago
Anonymous , Try a meausre like

calculate(distinctcount(vw_rpg_census[id]) , filter(vw_rpg_census, vw_rpg_census[status]="Active" && vw_rpg_census[is_available]=1))

Few more example in SQL vs DAX series - https://www.youtube.com/playlist?list=PLPaNVDMhUXGZNyKU0PgG2g3P0c6CPjMnj

USe this it in visual with Org_level (un summarized)
- Anonymous
  Not applicable
  5 years ago
  amitchandak ,
  I need to place the each org level by 92 in a funel as percentage.
  like ,1/92*100 for chief executive..
  the measure i am using -
  calculate(distinctcount(vw_rpg_census[Org_level]) , vw_rpg_census[status]="Active" , vw_rpg_census[is_available]=1)/92(measure)
  
  and the funel -
  
  it's not showing the correct figure. Like, chief executive officer should be 1.08 % but here its showing 100%
  - amitchandak
    Super User
    5 years ago
    Anonymous , Try like
    
    N= calculate(distinctcount(vw_rpg_census[id]) , filter(vw_rpg_census, vw_rpg_census[status]="Active" && vw_rpg_census[is_available]=1))
    
    D =calculate(distinctcount(vw_rpg_census[id]) , filter(allselected(vw_rpg_census), vw_rpg_census[status]="Active" && vw_rpg_census[is_available]=1))
    
    D need a sumx version
    
    D=
    Sumx(values(vw_rpg_census[Org_level]),calculate(distinctcount(vw_rpg_census[id]) , filter(allselected(vw_rpg_census), vw_rpg_census[status]="Active" && vw_rpg_census[is_available]=1))
    )
    
    New have %
    
    percent= Divide([N],[D])